#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import sys
import MySQLdb

class db:
	def __init__(self, db_host, db_port, db_name, db_user, db_passwd):
		"""インスタンスを作成"""
		self.db_host = db_host
		self.db_port = db_port
		self.db_name = db_name
		self.db_user = db_user
		self.db_passwd = db_passwd

		self.db_charset = 'utf8'
		self.db_tablePrefix = 'netebbms_'

		self.db_table_animation = 'animation'


	def connect(self):
		"""データベースに接続"""
		try:
			self.myDBMS = MySQLdb.connect(
				db = self.db_name,
				user = self.db_user,
				passwd = self.db_passwd,
				host = self.db_host)

			self.cursor_select = self.myDBMS.cursor()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def close(self):
		"""データベースを切断"""
		self.cursor_select.close()
		self.myDBMS.close()


	def init_database(self):
		"""データベースを初期化（テーブルの定義）"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute("""
				CREATE TABLE IF NOT EXISTS `%s%s` (
					`animation_id`  INT  NOT NULL  AUTO_INCREMENT,
					`generator`  TEXT  NOT NULL,
					`memo`  TEXT  NOT NULL,
					`created_time`  DOUBLE  NOT NULL,
					`model`  TINYTEXT  NOT NULL,
					`enable`  ENUM('True','False')  NOT NULL  DEFAULT 'False',
					`mode`  SMALLINT  NOT NULL,
					`count`  SMALLINT  NOT NULL,
					`end_time`  DOUBLE  NOT NULL,
					`frame_count`  SMALLINT  NOT NULL,
					`frame_filename`  TEXT  NOT NULL,
					PRIMARY KEY(`animation_id`)
				) ENGINE=MyISAM DEFAULT CHARSET=`%s`
				""" % (self.db_tablePrefix, self.db_table_animation, self.db_charset))
			cursor.close()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def fetchAnimation(self, model):
		"""アニメーション テーブルにSQLを発行"""
		try:
			self.cursor_select.execute("""
				SELECT `animation_id`, `created_time`, `mode`, `count`, `end_time`, `frame_filename`
				FROM `%s%s`""" % (self.db_tablePrefix, self.db_table_animation) + """
				WHERE `model` = %s AND `enable` = 'True'
				ORDER BY `animation_id` ASC
				""", (model))

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def getAnimationOne(self):
		"""アニメーション テーブルのデータを1件取得"""
		return self.cursor_select.fetchone()


	def getAnimationAll(self):
		"""アニメーション テーブルのデータを全件取得"""
		return self.cursor_select.fetchall()


	def getRowCount(self, model):
		"""データの件数を取得"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute("""
				SELECT `animation_id`
				FROM `%s%s`""" % (self.db_tablePrefix, self.db_table_animation) + """
				WHERE `model` = %s AND `enable` = 'True'
				""", (model))
			rowcount = cursor.rowcount
			cursor.close()

			return rowcount

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def setAnimation(self, generator, memo, created_time, model, enable, mode, count, end_time, frame_count, frame_filename):
		"""アニメーション テーブルにデータを追加"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute(("""
				INSERT INTO `%s%s` (`generator`, `memo`, `created_time`, `model`, `enable`, `mode`, `count`, `end_time`,
					`frame_count`, `frame_filename`)""" % (self.db_tablePrefix, self.db_table_animation)) + """
				VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
					(generator, memo, created_time, model, enable, mode, count, end_time, frame_count, frame_filename))
			cursor.close()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def updateAnimation(self, animation_id, enable, count):
		"""アニメーション テーブルのデータを更新"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute(("""
				UPDATE %s%s """ % (self.db_tablePrefix, self.db_table_animation)) + """
				SET `enable` = '%s', `count` = '%s'
				WHERE `animation_id` = %s""", (enable, count, animation_id))
			cursor.close()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def removeAnimationOne(self, animation_id):
		"""アニメーション テーブルのデータを1件削除"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute(("""
				DELETE FROM `%s%s`""" % (self.db_tablePrefix, self.db_table_animation)) + """
				WHERE `animation_id` = %s """, animation_id)
			cursor.close()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def removeAnimationAll(self):
		"""アニメーション テーブルのデータを全件削除"""
		try:
			cursor = self.myDBMS.cursor()
			cursor.execute("""DELETE FROM `%s%s`""" % (self.db_tablePrefix, self.db_table_animation))
			cursor.close()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex


	def commit(self):
		"""更新をデータベースに反映します"""
		try:
			self.myDBMS.commit()

		except MySQLdb.Error, ex:
			print >> sys.stderr, "Error %d: %s" % (ex[0], ex[1])

		except Exception, ex:
			print >> sys.stderr, ex

